﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DelayedQueue.Models;
using MySqlConnector;

namespace DelayedQueue.Data
{
    /// <summary>
    /// MySQL持久化管理器，负责任务的持久化存储
    /// </summary>
    public class MySQLPersistence : IDisposable
    {
        private readonly string _connectionString;
        private bool _isDisposed;

        public MySQLPersistence(string connectionString)
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
            InitializeDatabase();
        }

        /// <summary>
        /// 初始化数据库和表结构
        /// </summary>
        private void InitializeDatabase()
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                // 创建表结构（如果不存在）
                var createTableSql = @"
CREATE TABLE IF NOT EXISTS delayed_tasks (
    task_id VARCHAR(36) PRIMARY KEY,
    content TEXT NOT NULL,
    execute_time_utc DATETIME NOT NULL,
    status TINYINT NOT NULL,
    create_time_utc DATETIME NOT NULL,
    complete_time_utc DATETIME NULL,
    INDEX idx_execute_time_status (execute_time_utc, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

                using (var command = new MySqlCommand(createTableSql, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 保存任务
        /// </summary>
        public void SaveTask(DelayedTask task)
        {
            if (task == null) throw new ArgumentNullException(nameof(task));

            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                var sql = @"
INSERT INTO delayed_tasks 
(task_id, content, execute_time_utc, status, create_time_utc, complete_time_utc)
VALUES (@TaskId, @Content, @ExecuteTimeUtc, @Status, @CreateTimeUtc, @CompleteTimeUtc)
ON DUPLICATE KEY UPDATE
content = @Content,
execute_time_utc = @ExecuteTimeUtc,
status = @Status,
complete_time_utc = @CompleteTimeUtc;";

                using (var command = new MySqlCommand(sql, connection))
                {
                    AddParameters(command, task);
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 获取所有未完成的任务
        /// </summary>
        public List<DelayedTask> GetPendingTasks()
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                var sql = @"
SELECT task_id, content, execute_time_utc, status, create_time_utc, complete_time_utc
FROM delayed_tasks
WHERE status IN (0, 1)  -- Pending, Executing
ORDER BY execute_time_utc ASC;";

                using (var command = new MySqlCommand(sql, connection))
                using (var reader = command.ExecuteReader())
                {
                    return ReadTasks(reader).ToList();
                }
            }
        }

        /// <summary>
        /// 更新任务状态
        /// </summary>
        public void UpdateTaskStatus(string taskId, Models.TaskStatus status)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                var sql = @"
UPDATE delayed_tasks 
SET status = @Status,
    complete_time_utc = CASE WHEN @Status IN (2, 3, 4) THEN UTC_TIMESTAMP() ELSE NULL END
WHERE task_id = @TaskId;";

                using (var command = new MySqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@TaskId", taskId);
                    command.Parameters.AddWithValue("@Status", (int)status);
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 删除任务
        /// </summary>
        public void DeleteTask(string taskId)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                var sql = "DELETE FROM delayed_tasks WHERE task_id = @TaskId;";

                using (var command = new MySqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@TaskId", taskId);
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 添加参数到命令
        /// </summary>
        private void AddParameters(MySqlCommand command, DelayedTask task)
        {
            command.Parameters.AddWithValue("@TaskId", task.TaskId);
            command.Parameters.AddWithValue("@Content", task.Content);
            command.Parameters.AddWithValue("@ExecuteTimeUtc", task.ExecuteTimeUtc);
            command.Parameters.AddWithValue("@Status", (int)task.Status);
            command.Parameters.AddWithValue("@CreateTimeUtc", task.CreateTimeUtc);
            command.Parameters.AddWithValue("@CompleteTimeUtc",
                task.CompleteTimeUtc.HasValue ? (object)task.CompleteTimeUtc.Value : DBNull.Value);
        }

        /// <summary>
        /// 从数据读取器读取任务列表
        /// </summary>
        private IEnumerable<DelayedTask> ReadTasks(MySqlDataReader reader)
        {
            while (reader.Read())
            {
                yield return new DelayedTask
                {
                    TaskId = reader.GetString("task_id"),
                    Content = reader.GetString("content"),
                    ExecuteTimeUtc = reader.GetDateTime("execute_time_utc"),
                    Status = (Models.TaskStatus)reader.GetInt32("status"),
                    CreateTimeUtc = reader.GetDateTime("create_time_utc"),
                    CompleteTimeUtc = reader.IsDBNull(reader.GetOrdinal("complete_time_utc"))
                        ? null
                        : (DateTime?)reader.GetDateTime("complete_time_utc")
                };
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_isDisposed) return;
            _isDisposed = true;
        }

        ~MySQLPersistence()
        {
            Dispose(false);
        }
    }
}
